<?php
namespace Tlf\BigDb;
/**
* Extend this class to provide a CLI for your BigDb library.
*/
class LibraryCli extends \Tlf\Cli {
protected array $config = [];
protected \PDO $pdo;
public function __construct(){
parent::__construct();
}
/**
* add commands to cli
*/
public function init(){
$cli = $this;
$call = [$cli, 'call'];
$cli->load_command('main',
function($cli, $args){
$cli->call_command('help',[]);
}, "show this help menu"
);
$cli->load_command('list', $call, "List available queries");
$cli->load_command('print',$call, "Print a statement. Usage: bigdb print [query_name]");
$cli->load_command('exec',$call, "Execute a statement. Usage: bigdb exec [query_name] [-bound_arg \"value\"]");
$cli->load_command('query',$call, "Query via a statement and print rows. Usage: bigdb query [query_name] [-bound_arg \"value\"]");
$cli->load_command('recompile', $call, "Recompile the sql files");
$cli->load_command('migrate', $call, "Migrate an app. Usage: bigdb migrate [current_version] [target_version]");
$cli->load_command('list-migrations', $call, "Show available migrations. Usage: bigdb list-migrations");
$cli->load_command('explain', $call, "Show the execution plan for a stored query. Usage: bigdb explain [query_name] [-bound_arg \"value\"]");
$cli->load_command('sql', $call, "Run a raw SQL query on the database");
}
/**
* Get a PDO instance from stored config. Does not load config or perform any checks.
*
* @return PDO
*/
public function getPdo(): \PDO {
if (isset($this->pdo))return $this->pdo;
$config = $this->config;
$this->pdo = new \PDO('mysql:dbname='.$config['mysql.database'].';host='.$config['mysql.host'], $config['mysql.user'], $config['mysql.password']);
return $this->pdo;
}
/**
* Get a BigDb instance from stored config. Does not load config or perform any checks.
*
* @TODO allow for multiple BigDb apps
* @TODO scan for BigDb subclasses (instead of using config). Or maybe use array-based config? idk.
*/
public function getBigDb(): \Tlf\BigDb {
$config = $this->config;
$pdo = $this->getPdo();
$class = $config['app.class'];
$dir = $config['app.dir'];
$root_dir =
isset($config['app.root_dir'])
? $config['app.root_dir']
: $this->pwd;
$db = new $class($pdo, $root_dir.'/'.$dir); // this is part of BigDb's tests.
//$db->root_dir = ;
//$db->addSqlDir($dir.'/sql/', $force_recompile = false); // To add your own sql from `.sql` files on-disk. set force_recompile `true` during development of these files.
//$db->init_from_dir($root_dir.'/'.$dir.'/');
//$db->init_sql();
return $db;
}
/**
* List available queries
*
* @param $args not used
* @param $named_args not used
*/
public function cmd_list(array $args, array $named_args){
$config = $this->config;
$db = $this->getBigDb();
$sql = $db->getSql();
$queries = array_keys($sql);
echo "\nRun queries with '[binscript] query [query_name];'\nAvailable Queries:\n";
foreach($queries as $name){
echo "\n - $name";
}
echo "\n\n";
}
/**
* Print an sql statement.
*
* @param $args first param should be name of statement to print
* @param $named_args not used
*/
public function cmd_print(array $args, array $named_args){
$db = $this->getBigDb();
$sql = $db->getSql();
$statement_name = $args[0];
$statement = $sql[$statement_name];
echo "Statement '$statement_name':\n\n";
echo $statement;
}
/**
* Execute an sql statement & print number of rows affected.
*
* @param $args first param should be name of statement to print
* @param $named_args array of values to bind to the statement
*/
public function cmd_exec(array $args, array $named_args){
// @TODO provide error handling for no command specified or non-existent command. Maybe list available & prompt for which one
$db = $this->getBigDb();
$statement_name = $args[0];
$rows_affected = $db->exec($statement_name, $named_args);
echo "\nRows Affected: $rows_affected\n\n";
}
/**
* Query via stored SQL statement and print results.
*
* @param $args first param should be name of statement to print
* @param $named_args array of values to bind to the statement
*/
public function cmd_query(array $args, array $named_args){
// @TODO provide error handling for no command specified or non-existent command. Maybe list available & prompt for which one
$db = $this->getBigDb();
$statement_name = $args[0];
$rows = $db->query_rows($statement_name, $named_args);
$row_count = count($rows);
// @TODO normalize column widths for headers & row output.
echo "\n\n$row_count Rows:\n";
$header = array_keys($rows[0]);
foreach ($header as $name){
echo " $name | ";
}
$count = -1;
$this->print_table($rows);
//foreach ($rows as $row){
//$count++;
//echo "\n$count# ";
//foreach ($row as $key=>$value){
//echo "$value | ";
//}
//}
}
/**
* Run a raw SQL query on the database
*
* @usage bigdb sql SELECT * FROM articles WHERE title LIKE "%whatever%"
*
* @param $args array<int index, string query_part>
* @param $named_args array not used
*/
public function cmd_sql(array $args, array $named_args){
$db = $this->getBigDb();
$sql = implode(" ",$args);
echo "\n$sql\n\n";
$results = $db->sql_query($sql);
//print_r($results);
$this->print_table($results);
}
/**
* run an EXPLAIN on a stored query.
*
* @param $args first param should be name of statement to print
* @param $named_args array of values to bind to the statement
*/
public function cmd_explain(array $args, array $named_args){
$db = $this->getBigDb();
$sql = $db->getSql();
$statement_name = $args[0];
$statement = $db->build_query($statement_name, $named_args);//sql[$statement_name];
$explain_sql = "EXPLAIN $statement";
$results = $db->sql_query($explain_sql);
$this->print_table($results);
}
/**
* Migrate from old version to target version
*
* @param $args $args[0] == old version, $args[1] == new version
* @param $named_args array of values to bind to the statement
*/
public function cmd_migrate(array $args, array $named_args){
$old_version = $args[0] ?? null;
$new_version = $args[1] ?? null;
if (!is_numeric($new_version)){
echo "Target version must be an int";
return;
}
if (!is_numeric($old_version)){
echo "Current version must be an int";
return;
}
if ($old_version==$new_version){
echo 'Current & target versions must be different';
return;
}
$db = $this->getBigDb();
$db->migrate($old_version, $new_version);
}
/**
* Show available migration versions
*
* @param $args $args[0] == old version, $args[1] == new version
* @param $named_args array of values to bind to the statement
*/
public function cmd_list_migrations(array $rgs, array $named_args){
$migrations = $this->getBigDb()->get_migrations();
foreach ($migrations as $m){
$output = [];
if (isset($m['up']))$output[] = $m['up'];
if (isset($m['down']))$output[] = $m['down'];
$print =
" - Version ".$m['version'].': '
.implode(", ", $output);
echo "\n".$print;
}
}
/**
* Recompile sql files
*
* @param $args not used
* @param $named_args not used
*/
public function cmd_recompile(array $args, array $named_args){
$db = $this->getBigDb();
$db->recompile_sql();
echo "SQL Recompiled";
}
/**
* Call a cli command
* @param $cli arg not used, because it should always be this object
* @param $args array of args, with $args['--'] containing unnamed args.
*/
public function call(\Tlf\BigDb\LibraryCli $cli, array $args){
// SETUP
$command = $this->command;
$unnamed_args = $args['--'] ?? [];
unset($args['--']);
$func = 'cmd_'.str_replace('-','_',$command);
if (!method_exists($this, $func)){
echo "\nCommand '$command' does not exist\n";
return;
}
$stored_configs = $this->get_stored_configs($args);
$this->config = array_merge($stored_configs, $this->args);
// RUN
echo "\n";
$this->$func($unnamed_args, $args);
echo "\n";
}
/** get configs stored at config/bigdb.json or .config/bigdb.json */
protected function get_stored_configs(array $args): array {
if (isset($args['config_location'])){
$path = $this->pwd.'/'.$args['config_location'];
if (!is_file($path)){
throw new \Exception("Config file not found at '".$args['config_location']."'");
}
return json_decode(file_get_contents($path), true, 512, JSON_THROW_ON_ERROR);
} else {
$config_locations = [
'config/bigdb.json',
'.config/bigdb.json'
];
$path = null;
foreach ($config_locations as $rel_path){
$abs_path = $this->pwd.'/'.$rel_path;
if (is_file($abs_path)){
$path = $abs_path;
$config = $rel_path;
break;
}
}
if ($path !== null){
return json_decode(file_get_contents($path), true, 512, JSON_THROW_ON_ERROR);
}
throw new \Exception("Config file not found at 'config/bigdb.json' or '.config/bigdb.json'");
}
}
/**
* Print an array as a table, just like mysql cli does. all values will be printed. column width will be fixed to the maximum length
*
* @param $rows array<string key, mixed value> keys will be printed as headers.
*/
public function print_table(array $rows){
$stats = [];
foreach ($rows as $rownum=>$row){
foreach ($row as $key=>$value){
$cur_len = $stats[$key] ?? 0;
if (($new_len = strlen($value)) > $cur_len)$stats[$key] = $new_len;
if (($keylen = strlen($key)) > $new_len) $stats[$key] = $keylen;
}
}
$total_len = 0;
$lines = ["","",""];
$last_line = '';
foreach ($stats as $key=>$len){
$pluspart = "+". str_repeat('-',$len + 2);
$lines[0] .= $pluspart;
$lines[1] .= "| $key ";
$lines[2] .= $pluspart;
$last_line .= $pluspart;
if (($diff = ($len - strlen($key) ) ) >0)$lines[1] .= str_repeat(' ',$diff);
}
$lines[0] .= '+';
$lines[1] .= '|';
$lines[2] .= '+';
foreach ($lines as $line){
echo "\n".$line;
}
foreach ($rows as $row){
echo "\n";
foreach ($row as $key=>$value){
$value = str_replace("\n", "\\n", $value);
echo "| ".$value .' ';
$maxlen = $stats[$key] ?? 0;
$thislen = strlen($value);
if (($diff = ($maxlen - $thislen)) > 0) echo str_repeat(" ", $diff);
}
echo "|";
}
echo "\n{$last_line}+";
echo "\n";
// EXAMPLE ... except we're not right-justifying nulls & numbers
//+----+---------------+-------------+--------------+---------------------+---------+--------------------+
//| id | title | description | slug | created_at | status | related_article_id |
//+----+---------------+-------------+--------------+---------------------+---------+--------------------+
//| 1 | One | Desc 1 | one | 2023-11-27 15:31:01 | public | NULL |
//| 2 | Two | Desc 2 | two | 2023-11-27 15:31:01 | public | NULL |
//| 3 | Three | Desc 3 | three | 2023-11-27 15:31:01 | public | 1 |
//| 4 | Four, Private | Desc 4 | four-private | 2023-11-27 15:31:01 | private | 1 |
//+----+---------------+-------------+--------------+---------------------+---------+--------------------+
}
}